﻿using Common;
using DbUtility;
using STMS.Models.DModels;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace STMS.DAL
{
    public class StoreRegionDAL:BaseDAL<StoreRegionInfo>
    {
        /// <summary>
        /// 添加仓库分区
        /// </summary>
        /// <param name="srInfo"></param>
        /// <param name="isGetId"></param>
        /// <returns></returns>
        public int AddStoreRegion(StoreRegionInfo srInfo,int isGetId)
        {
            string cols = "SRegionName,SRegionNo,StoreId,SRTemperature,Remark";
            //return Add(srInfo, cols, isGetId);
            //添加分区后，修改仓库的分区数
            return SqlHelper.ExecuteTrans<int>(cmd =>
            {
                try
                {
                    SqlModel addModel = CreateSql.GetInsertSqlAndParas(srInfo, cols, isGetId);
                    cmd.CommandText = addModel.Sql;
                    SqlHelper.AddParas(cmd, addModel.SqlParaArray);
                    int re = 0;
                    if (isGetId == 0)
                        re = cmd.ExecuteNonQuery();
                    else
                    {
                        object oId = cmd.ExecuteScalar();
                        re = oId.GetInt();
                    }
                    cmd.Parameters.Clear();
                    if (re > 0)
                    {
                        string upSql = $"update StoreInfos set RegionCount=RegionCount+1 where StoreId={srInfo.StoreId}";
                        cmd.CommandText = upSql;
                        cmd.ExecuteNonQuery();
                        cmd.Transaction.Commit();
                    }
                    return re;
                }
                catch(Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception("执行添加仓库分区信息异常！", ex);
                }
            });
        }

        /// <summary>
        /// 修改仓库分区信息
        /// </summary>
        /// <param name="srInfo"></param>
        /// <returns></returns>
        public bool UpdateStoreRegion(StoreRegionInfo srInfo,int oldStoreId)
        {
            string cols = "SRegionId,SRegionName,SRegionNo,StoreId,SRTemperature,Remark,TemperState";
            if (srInfo.AllowHighTemperature != null)
                cols += ",AllowHighTemperature";
            if (srInfo.AllowLowTemperature != null)
                cols += ",AllowLowTemperature";
            //return Update(srInfo, cols);
            return SqlHelper.ExecuteTrans<bool>(cmd =>
            {
                try
                {
                    SqlModel updateModel = CreateSql.GetUpdateSqlAndParas(srInfo, cols,"");
                    cmd.CommandText = updateModel.Sql;
                    SqlHelper.AddParas(cmd, updateModel.SqlParaArray);
                    int re = 0;
                    re = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    if (re > 0 &&oldStoreId>0)
                    {
                        string upSql = $"update StoreInfos set RegionCount=RegionCount+1 where StoreId={srInfo.StoreId}";
                        cmd.CommandText = upSql;
                        cmd.ExecuteNonQuery();
                        string upSql1 = $"update StoreInfos set RegionCount=RegionCount-1 where StoreId={oldStoreId}";
                        cmd.CommandText = upSql;
                        cmd.ExecuteNonQuery();
                     
                    }
                    cmd.Transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception("执行添加仓库分区信息异常！", ex);
                }
            });
        }

        /// <summary>
        /// 修改室内温度（升温或降温处理）
        /// </summary>
        /// <param name="regionId"></param>
        /// <param name="srTemper"></param>
        /// <returns></returns>
        public bool UpdateRegionTemperature(int regionId,decimal srTemper)
        {
            string sql = $"update StoreRegionInfos set SRTemperature={srTemper},TemperState=1 where SRegionId={regionId} ";
            return SqlHelper.ExecuteNonQuery(sql, 1)>0;
        }

        /// <summary>
        /// 更新仓库分区的温度值
        /// </summary>
        /// <param name="regions"></param>
        /// <returns></returns>
        public bool UpdateRegionsTemperatures(List<StoreRegionInfo> regions)
        {
            if(regions.Count>0)
            {
                List<string> comList = new List<string>();
                foreach(StoreRegionInfo region in regions)
                {
                    string sql = $"update StoreRegionInfos set SRTemperature={region.SRTemperature},TemperState={region.TemperState} where SRegionId={region.SRegionId} ";
                    comList.Add(sql);
                }
                return SqlHelper.ExecuteTrans(comList);
            }
            return false;
        }

        /// <summary>
        /// 获取指定仓库下的分区
        /// </summary>
        /// <param name="storeId"></param>
        /// <returns></returns>
        public List<StoreRegionInfo> GetRegionsByStoreId(int storeId)
        {
            string strWhere = $"StoreId={storeId} and IsDeleted=0";
            return GetModelList(strWhere, "SRegionId,SRegionName");
        }

        /// <summary>
        /// 检查指定分区是否已存储产品
        /// </summary>
        /// <param name="regionId"></param>
        /// <returns></returns>
        public DataTable GetRegionProductRecords(int regionId)
        {
            string sql1 = $"select sum(ProductCount) from ProductStoreInfos where SRegionId={regionId}";
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql1, 1);
            return dt;
        }
        /// <summary>
        /// 指定分区是否已进行设置了温度（进行了温度监控即投入使用）
        /// </summary>
        /// <param name="regionId"></param>
        /// <returns></returns>
        public DataTable IsRegionSetTemperature(int regionId)
        {
            string sql1 = $"select count(1) from SRegionTemperatureRecordInfos where SRegionId={regionId}";
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql1, 1);
            return dt;
        }

        /// <summary>
        /// 获取指定的分区信息
        /// </summary>
        /// <param name="regionId"></param>
        /// <returns></returns>
       public StoreRegionInfo GetRegion(int regionId)
        {
            string cols = CreateSql.GetColsString<StoreRegionInfo>("IsDeleted");
            return GetById(regionId, cols);
        }

    }
}
